const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 *入库接口
 */

//  入库开始时间
let startTime = '2019-04-08 00:00:00';
// let startTime = '';
//  获得订单信息
const getStorageOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,J.XH,B.DWLX,Z.RQ,Z.ID,Z.DWBM,Z.BMBM,Z.BMMC,Z.DWMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.JBRMC,Z.PZH,Z.ZJS,Z.ZSS,Z.ZHJ,J.KM1BM,J.KM2BM,J.KM3BM,J.KMQC FROM DHD_ZB Z LEFT JOIN DHD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM LEFT JOIN (SELECT SUM(SL) AS SL,ID FROM DHD_MX GROUP BY ID) M ON Z.ID  = M.ID LEFT JOIN (SELECT SUM(RM.SL) AS BJSL,RZ.ID_DHD FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID GROUP BY RZ.ID_DHD ) T1 ON Z.ID = T1.ID_DHD AND M.SL<=T1.BJSL WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND Z.RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}'  AND T1.ID_DHD IS NULL AND Z.SH = 'T' `;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL FROM DHD_ZB Z LEFT JOIN DHD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM LEFT JOIN (SELECT SUM(SL) AS SL,ID FROM DHD_MX GROUP BY ID) M ON Z.ID  = M.ID LEFT JOIN (SELECT SUM(RM.SL) AS BJSL,RZ.ID_DHD FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID GROUP BY RZ.ID_DHD ) T1 ON Z.ID = T1.ID_DHD AND M.SL<=T1.BJSL WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}'  AND T1.ID_DHD IS NULL AND Z.SH = 'T' `
    if (body.searchVal.JSFSBM) {
        startSql += ` AND Z.JSFSBM='${body.searchVal.JSFSBM}'`;
        totalSql += ` AND Z.JSFSBM='${body.searchVal.JSFSBM}'`;
    }
    if (body.searchVal.GYS) {
        startSql += ` AND (B.BM LIKE '%${body.searchVal.GYS}%' OR B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%') `;
        totalSql += ` AND (B.BM LIKE '%${body.searchVal.GYS}%' OR B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%')`;
    }
    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    startSql += endSql;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}
// 把剂型的中文转成英文
const judgeJx = (jx) => {
    let result = '';
    let jxList = {
        P: '片剂',
        J: '胶囊',
        W: '丸剂',
        Y: '液体制剂',
        G: '固体制剂'
    }
    for (let item in jxList) {
        if (jx.replace(/\s+/g, "") == jxList[item]) {
            result = item;
        }
    }
    return result;
}
//  获得订单对应的商品信息
const getStorageOrderShop = async (ctx, next) => {
    const body = ctx.request.body;
    // 有经营范围筛选的
    // let startSql = `SELECT * FROM (SELECT M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,SUM(M.SL) AS SL,SUM(M.JE) AS JE,Y.CCTJ,SUM(Y.MJSL) AS MJSL,SUM(M.JS) AS JS,SUM(M.SS) AS SS,M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,SUM(M.HJ) AS HJ,Y.JG_FLAG,Y.YXFL,Y.JYFL,Y.JP,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ,L.LSDJ FROM DHD_MX M LEFT JOIN BM_YP Y ON M.YPBM =  Y.BM LEFT JOIN (SELECT  MIN(YPPHGZ) AS YPPHGZ,YPBM FROM YPPHGZ GROUP BY YPBM) G ON M.YPBM = G.YPBM LEFT JOIN (SELECT MIN(DJ) AS LSDJ,YPBM FROM RKD_MX GROUP BY YPBM)L ON M.YPBM = L.YPBM  WHERE M.ID = '${body.orderId}' GROUP BY Y.JYFL,Y.JP,L.LSDJ,M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,Y.CCTJ,M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,Y.JG_FLAG,Y.YXFL,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ) t1 LEFT JOIN (SELECT * FROM (SELECT R.YPBM AS BJYPBM,SUM(R.SL) AS BJSL FROM RKD_MX R LEFT JOIN RKD_ZB Z ON R.ID = Z.ID WHERE Z.ID_DHD = '${body.orderId}' GROUP BY R.YPBM ) t3 WHERE t3.BJYPBM IS NOT NULL) t2 ON t2.BJYPBM = t1.YPBM AND t2.BJSL < t1.SL WHERE t2.BJSL IS NOT NULL OR t1.YPBM <>ALL (SELECT R.YPBM FROM RKD_MX R LEFT JOIN  RKD_ZB Z ON R.ID = Z.ID WHERE Z.ID_DHD = '${body.orderId}') AND (SELECT JYFW FROM BM_BM WHERE BM = '${body.BMBM}') LIKE '%'+t1.JYFL+'%' `;
    // 没有经营范围筛选的
    let startSql = `SELECT * FROM (SELECT M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,SUM(M.SL) AS SL,SUM(M.JE) AS JE,Y.CCTJ,SUM(Y.MJSL) AS MJSL,SUM(M.JS) AS JS,SUM(M.SS) AS SS,M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,SUM(M.HJ) AS HJ,Y.JG_FLAG,Y.YXFL,Y.JYFL,Y.JP,Y.CB_FLAG,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ,L.LSDJ,M.ZXYXQX FROM DHD_MX M LEFT JOIN BM_YP Y ON M.YPBM =  Y.BM LEFT JOIN (SELECT  MIN(YPPHGZ) AS YPPHGZ,YPBM FROM YPPHGZ GROUP BY YPBM) G ON M.YPBM = G.YPBM LEFT JOIN (SELECT MIN(DJ) AS LSDJ,YPBM FROM RKD_MX GROUP BY YPBM)L ON M.YPBM = L.YPBM  WHERE M.ID = '${body.orderId}' GROUP BY Y.JYFL,Y.JP,L.LSDJ,M.YPBM,M.YPMC,M.YPGG,Y.CB_FLAG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,Y.CCTJ,M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,Y.JG_FLAG,Y.YXFL,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ,M.ZXYXQX) t1 LEFT JOIN (SELECT * FROM (SELECT R.YPBM AS BJYPBM,SUM(R.SL) AS BJSL FROM RKD_MX R LEFT JOIN RKD_ZB Z ON R.ID = Z.ID WHERE Z.ID_DHD = '${body.orderId}' GROUP BY R.YPBM ) t3 WHERE t3.BJYPBM IS NOT NULL) t2 ON t2.BJYPBM = t1.YPBM AND t2.BJSL < t1.SL WHERE t2.BJSL IS NOT NULL OR t1.YPBM <>ALL (SELECT R.YPBM FROM RKD_MX R LEFT JOIN  RKD_ZB Z ON R.ID = Z.ID WHERE Z.ID_DHD = '${body.orderId}')  `;

    // if(body.DWLX == 1 || body.DWLX == 2){
    //     startSql+= ` AND (SELECT JYFW FROM BM_WLDW WHERE BM = '${body.DWBM}') LIKE '%'+t1.JYFL+'%'`;
    // }
    let result = await sql.query(startSql);
    ctx.body = {
        data: result.recordset
    };
}
// 获得三天前的时间
// const getThreeDayAgo = () => {
//     let nowStamp = (new Date()).getTime();
//     let threeAgoStamp = nowStamp - 3 * 24 * 60 * 60 * 1000;
//     let threeAgoTime = new Date(threeAgoStamp);
//     let y = threeAgoTime.getFullYear();
//     let m = threeAgoTime.getMonth() + 1;
//     let d = threeAgoTime.getDate();
//     return y + '-' + (m < 10 ? ('0' + m) : m) + '-' + (d < 10 ? ('0' + d) : d);
// }

//  给商品分配货位架
const getStorageShopCarNum = async (ctx, next) => {
    const ypbmList = ctx.request.body.ypbmList;
    // let matchHwbh = [];
    let reulstYpbmList = {};
    for (let item in ypbmList) {
        let hwbhResult = await sql.query(`SELECT HWBH FROM YP_HWBH WHERE YPBM = '${ypbmList[item]}' AND BMBM = '07' `);
        if (0 in hwbhResult.recordset) {
            reulstYpbmList[ypbmList[item]] = hwbhResult.recordset[0].HWBH;
        } else {
            reulstYpbmList[ypbmList[item]] = '';
        }
        // let isHwbhKc_kcdtb = await sql.query(`SELECT HWBH FROM KC_KCDTB WHERE YPBM = '${ypbmList[item]}' AND HWBH != '' AND KCSL > 0`);
        // if (isHwbhKc_kcdtb.recordset[0]) {
        //     reulstYpbmList[ypbmList[item]] = isHwbhKc_kcdtb.recordset[0].HWBH;
        // }
        // else {
        //     let isHwbhRkd_mx = await sql.query(`SELECT TOP 1 HWBH FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID WHERE Z.SH = 'F' AND M.YPBM = '${ypbmList[item]}'  AND HWBH != '' AND Z.RQ >='${baseFn.getDayAgo(3)} 00:00:00.000' ORDER BY Z.RQ DESC `);
        //     if (isHwbhRkd_mx.recordset[0]) {
        //         reulstYpbmList[ypbmList[item]] = isHwbhRkd_mx.recordset[0].HWBH;
        //     } else {
        //         let isHwbhXtd_mx = await sql.query(`SELECT TOP 1 M.HWBH FROM XTD_MX M LEFT JOIN XTD_ZB Z ON M.ID = Z.ID WHERE Z.SH = 'F' AND M.YPBM = '${ypbmList[item]}'  AND M.HWBH != '' AND Z.RQ >='${baseFn.getDayAgo(3)} 00:00:00.000' ORDER BY Z.RQ DESC `);
        //         if (isHwbhXtd_mx.recordset[0]) {
        //             reulstYpbmList[ypbmList[item]] = isHwbhXtd_mx.recordset[0].HWBH;
        //         } else {
        //             let searchShop = await sql.query(`SELECT * FROM YPZL L WHERE L.YPBM = '${ypbmList[item]}'`);
        //             let hwlx = '';
        //             let hwbhRangeSql = `SELECT H.HWBH FROM HWBH H WHERE H.HWBH <>ALL (SELECT M.HWBH FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID WHERE Z.SH = 'F' AND  M.HWBH != '') AND H.HWBH <>ALL (SELECT HWBH FROM KC_KCDTB K WHERE K.KCSL >0 AND K.HWBH != '' ) `;
        //             if (searchShop.recordset.length === 0) {
        //                 reulstYpbmList[ypbmList[item]] = '';
        //             } else {
        //                 if (searchShop.recordset[0].SPLX === 'Y') {
        //                     hwlx = searchShop.recordset[0].SPLX + searchShop.recordset[0].NFWY + judgeJx(searchShop.recordset[0].JXMC);
        //                     hwbhRangeSql += `AND H.HWLX = '${hwlx}' AND H.HWBH LIKE '__${searchShop.recordset[0].CCTJ}%'`
        //                 } else {
        //                     hwlx = searchShop.recordset[0].SPLX;
        //                     hwbhRangeSql += `AND H.HWLX = '${hwlx}' `
        //                 }
        //                 hwbhRangeSql += ` ORDER BY H.HWBH`;
        //                 let hwbhRange = await sql.query(hwbhRangeSql);
        //                 if (hwbhRange.recordset.length > 0) {
        //                     hwbhRange.recordset = hwbhRange.recordset.filter(rangeItem => {
        //                         return !matchHwbh.includes(rangeItem.HWBH.replace(/\s+/g, ""))
        //                     });
        //                     if (hwbhRange.recordset.length > 0) {
        //                         reulstYpbmList[ypbmList[item]] = hwbhRange.recordset[0].HWBH;
        //                         matchHwbh.push(hwbhRange.recordset[0].HWBH.replace(/\s+/g, ""));
        //                         if (item === ypbmList.length - 1) {
        //                             resolve(reulstYpbmList);
        //                         }
        //                     } else {
        //                         reulstYpbmList[ypbmList[item]] = '';
        //                     }
        //                 }
        //             }
        //         }
        //     }

        // }

    }

    ctx.body = {
        success: true,
        data: reulstYpbmList
    };

}


// 获得有效期至
const getValidityTimeTo = (m) => {
    let timeStamp = new Date().getTime();
    let trasformStamp = m * 86400000;
    let validityTime = new Date(timeStamp + trasformStamp);
    return baseFn.getNowTimeId(validityTime).fullDate
}
// 计算抽样数量
const computeCYSL = (sl, mjsl) => {

    sl = sl || 0;
    mjsl = mjsl || 0;
    if (sl !== 0 && mjsl !== 0) {
        let limitSL = sl / mjsl;
        let cysl = 0;
        if (limitSL <= 1) {
            cysl = 3;
        } else if (1 < limitSL && limitSL <= 50) {
            cysl = 6;
        } else {
            cysl = (Math.ceil(sl / (mjsl * 50)) + 1) * 3;
        }
        return cysl;
    } else {
        return 3;
    }

}

//  获得订单信息
const keepStorageInfor = async (ctx, next) => {
    try {
        const body = ctx.request.body;
        let shopInfor = body.entryList;
        let rkdId = await baseFn.createId('0714', 'RKD_ZB');
        let shdId = await baseFn.createId('0714', 'SHD_ZB');
        let ysdId = await baseFn.createId('0714', 'YSD_ZB');
        let actionSqlList = [];
        /**
         * 写入入库明细表
         */
        let insertMxSql = `INSERT INTO RKD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,YXQZ,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,ZK,DJ,
        DJ_HS,JE,RT,SE,HJ,ZKJE,CB,JG,FLAG,CKBM,CKMC,HWBH,YXFL,BZ,PFJ,LSJ,HYJ,UID) `;

        /**
         * 写入采购收货单明细
         */
        let insertSHDMxSql = `INSERT INTO SHD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,YXQZ,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,ZK,DJ,
        DJ_HS,JE,RT,SE,HJ,PFJ,LSJ,HYJ,CB,BZ) `;

        /**
     * 写入采购验收单明细
     */
        let insertYSDMxSql = `INSERT INTO YSD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,YXQZ,JLDW,MJSL,DHRQ,SL,JS,SS,HGSL,JYBGS,
        YSJL,ZLZK,CY_SL,CY_HG,CY_WG,CY_BZ,CY_BQ,CY_SMS,CY_HGZ,YJ,YJ_HS,ZK,DJ,DJ_HS,JE,RT,SE,HJ,PFJ,LSJ,HYJ,CB) `;

        // 把时间段转成天数
        let transformTimeToDay = ({ SCRQ, YXQZ }) => {
            let scrqStamp = new Date(SCRQ).getTime();
            let yxqzStamp = new Date(YXQZ).getTime();
            return Math.round((yxqzStamp - scrqStamp) / 86400000)
        }

        /**
         * 更新批号规则
         */
        for (let item of shopInfor) {
            let isHavePHGZSql = `SELECT COUNT(YPBM) AS SL FROM YPPHGZ WHERE YPBM = '${item.YPBM}'`;
            let isHavePHGZ = await sql.query(isHavePHGZSql);
            let GzSql = '';
            if (isHavePHGZ.recordset[0].SL === 0) {
                GzSql = `INSERT INTO YPPHGZ VALUES ('${item.YPBM}','${item.YPPHGZ}')`;
            } else {
                GzSql = `UPDATE YPPHGZ SET YPPHGZ = '${item.YPPHGZ}' WHERE YPBM = '${item.YPBM}'`;
            }
            actionSqlList.push(GzSql);
            actionSqlList.push(`UPDATE BM_YP SET YXQX = '${transformTimeToDay({ SCRQ: item.SCRQ, YXQZ: item.YXQZ })}' WHERE BM= '${item.YPBM}'`);
        };
        let zje = 0, zss = 0, zjs = 0, zsl = 0;

        shopInfor.forEach((item, ind) => {
            let dj = Number(item.DJ ? item.DJ : 0);
            let shsl = Number(item.SHSL ? item.SHSL : 0);
            let mjsl = Number(item.MJSL ? item.MJSL : 0);
            let je = dj * shsl;
            let js = mjsl === 0 ? 0 : Math.floor(shsl / mjsl);
            let ss = shsl - (mjsl * js);
            zje += je;
            zss += ss;
            zjs += js;
            zsl += shsl;
            let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);
            /**
             * 写入入库明细表
             */
            insertMxSql += `SELECT '${rkdId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM}','${item.YPMC}',
        '${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}',
        '${item.JLDW}',${item.MJSL},${item.SHSL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},${item.DJ_HS},${je},0,0,${je},0,'${item.CB_FLAG}',
        '${item.JG_FLAG}','','${item.CCTJ == "常温" ? '0701' : '0702'}','${item.CCTJ}库','${item.HWBH.toUpperCase()}','${item.YXFL}','',
        ${item.PFJ},${item.LSJ},${item.HYJ},'' `;
            /**
             * 写入采购收货单明细表
             */
            insertSHDMxSql += `SELECT '${shdId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM}','${item.YPMC}',
        '${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}',
        '${item.JLDW}',${item.MJSL},${item.SHSL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},${item.DJ_HS},${je},0,0,${je},
        ${item.PFJ},${item.LSJ},${item.HYJ},'${item.CB_FLAG}','' `;
            /**
            * 写入采购验收单明细
            */
            insertYSDMxSql += `SELECT '${ysdId}',${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM}','${item.YPMC}',
        '${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}',
        '${item.JLDW}',${item.MJSL},'${baseFn.getNowTimeId().fullDate}',${item.SHSL},${js},${ss},${item.SHSL},'有','合格','合格',
        ${computeCYSL(item.SHSL, item.MJSL)},${computeCYSL(item.SHSL, item.MJSL)},'合格','合格','合格','有','有',${item.YJ},${item.YJ_HS},100,
        ${item.DJ},${item.DJ_HS},${je},0,0,${je},${item.PFJ},${item.LSJ},${item.HYJ},'${item.CB_FLAG}' `;
            if (ind !== shopInfor.length - 1) {
                insertMxSql += ` UNION `;
                insertSHDMxSql += ` UNION `;
                insertYSDMxSql += ` UNION `;

            }
        });
        /**
         * 写入入库结算表
         */
        let insertJsSql = `INSERT INTO RKD_JS (ID,XH,BM,MC,KM1BM,KM2BM,KM3BM,KMBM,KMQC,JSJE,PH) SELECT '${rkdId}',1,'10','月结','${body.chooseOrderList.KM1BM}','${body.chooseOrderList.KM2BM}','${body.chooseOrderList.KM3BM}','1001','${body.chooseOrderList.KMQC}',${zje},''`;
        /**
         * 写入采购验收单总表
         */
        let insertYSDZbSql = `INSERT INTO YSD_ZB (ID,RQ,BMBM,BMMC,DWBM,DWMC,JBRBM,JBRMC,ZDR,SHR,ZSL,ZJS,ZSS,ZJE,ZSE,ZHJ,SH ) SELECT 
    '${ysdId}','${baseFn.getNowTimeId().fullDate}','${body.chooseOrderList.BMBM}','${body.chooseOrderList.BMMC}','${body.chooseOrderList.DWBM}',
    '${body.chooseOrderList.DWMC}','${body.queryList.YSR}','${body.queryList.YSRMC}',
    '${body.queryList.ZDR}','${body.queryList.SHRMC}',${zsl},${zjs},${zss},${zje},0.00,${zje},'T'`;
        /**
        /**
         * 写入采购收货单总表
         */
        let insertSHDZbSql = `INSERT INTO SHD_ZB (ID,RQ,BMBM,BMMC,DWBM,DWMC,JBRBM,JBRMC,YSDW,FHDD,YSGJ,QYSJ,DHSJ,WKFS,SHTX_JL,SHTX_BZ,YSGJ_JL,YSGJ_BZ,DHYP_JL,
        DHYP_BZ,ZDR,SHR,ZSL,ZJS,ZSS,ZJE,ZSE,ZHJ,SH ) SELECT 
    '${shdId}','${baseFn.getNowTimeId().fullDate}','${body.chooseOrderList.BMBM}','${body.chooseOrderList.BMMC}','${body.chooseOrderList.DWBM}',
    '${body.chooseOrderList.DWMC}','${body.queryList.SHOUHR}','${body.queryList.SHOUHRMC}','${body.queryList.YSDW}','${body.chooseOrderList.DWMC}',
    '${body.queryList.YSGJ}','','','NULL','合格','NULL','合格','NULL','合格','NULL','${body.queryList.ZDR}',
    '${body.queryList.SHRMC}',${zsl},${zjs},${zss},${zje},0.00,${zje},'T'`;
        /**
         * 写入入库总表
         */
        let insertZbSql = `INSERT INTO RKD_ZB (ID,RQ,DWBM,DWMC,BMBM,BMMC,JBRBM,JBRMC,JSFSBM,JSFSMC,ZDR,SHR,PZH,ZDZK,FKQX,FKQZ,PJLX,PJHM,ZSL,ZJS,ZSS,ZJE,
        ZSE,ZHJ,TJE,YJS,EJS,WJS,ZZK,ID_DHD,ZY,BZ,SH) SELECT 
    '${rkdId}','${baseFn.getNowTimeId().fullDate}','${body.chooseOrderList.DWBM}','${body.chooseOrderList.DWMC}','${body.chooseOrderList.BMBM}','
    ${body.chooseOrderList.BMMC}','${body.queryList.JBRBM}','${body.queryList.JBRMC}',
    '${body.chooseOrderList.JSFSBM ? body.chooseOrderList.JSFSBM : ''}','${body.chooseOrderList.JSFSMC}','${body.queryList.ZDR}',
    '${body.queryList.SHRMC}',NULL,100,${body.chooseOrderList.FKQX},'${body.chooseOrderList.FKQZ}',
    '${body.queryList.PJLX}','${body.queryList.PJHM}',${zsl},${zjs},${zss},${zje},0.00,${zje},0.00,${zje},0.00,${zje},0.00,
    '${body.chooseOrderList.ID}','','','F'`;
        console.log('chooseOrderList',body.chooseOrderList);

        actionSqlList.push(insertZbSql);
        actionSqlList.push(insertJsSql);
        actionSqlList.push(insertMxSql);
        actionSqlList.push(insertSHDZbSql);
        actionSqlList.push(insertSHDMxSql);
        actionSqlList.push(insertYSDZbSql);
        actionSqlList.push(insertYSDMxSql);
        let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;
        console.log('lastSql',lastSql);
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            let startSql = `SELECT Z.RQ,Z.JSFSMC,W.LLR,Z.ID,Z.DWMC,W.LXR,W.DH,Z.ID_DHD,M.YPMC,M.YPGG,M.JXMC,M.JLDW,M.SCCJ,M.PZWH,M.SCPH,M.YXQZ,M.SL,M.DJ,M.HJ,M.HWBH,Z.ZDR,Z.JBRMC,Z.BZ FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID LEFT JOIN BM_WLDW W ON M.YPBM = W.BM WHERE M.ID = '${rkdId}'`;
            let result = await sql.query(startSql);
            ctx.body = { success: true, result: result.recordset }
        } else {
            ctx.body = { success: false }
        }

    } catch (err) {
        ctx.body = { success: false, err };
    }
}


// 为供应商的编码匹配速拓的编码
const getSystemStorageMatchYpbh = async (ctx, next) => {
    const matchExcelList = ctx.request.body.matchExcelList;
    let gypbmList = [];
    matchExcelList.forEach(async (item, ind) => {
        gypbmList.push(`'${item.GYPBM}'`);
    })

    let allListSql = `SELECT * FROM GYSBMPP  WHERE GYPBM IN (${gypbmList.join(',')})`;
    let allListResult = await sql.query(allListSql);
    let lastListResult = allListResult.recordset;
    matchExcelList.forEach(async (item1, ind) => {
        let filterVal = lastListResult.filter((item2) => {
            return (item1.BM === item2.BM && item1.GYPBM === item2.GYPBM)
        })
        if (filterVal.length !== 0) {
            item1.YPBM = filterVal[0].YPBM
        }
    })
    ctx.body = matchExcelList;
}

// 获取入库员工的信息
const getStorageStaffInfo = async (ctx, next) => {
    let startSql = `SELECT BM,MC FROM BM_YG WHERE (GW LIKE '%验收岗%' OR GW LIKE '%收货岗%') AND QY_FLAG = 'T' AND BMBM = '07'`;
    let result = await sql.query(startSql);
    ctx.body = {
        result: result.recordset
    };
}



module.exports = {
    'POST /getStorageOrder': getStorageOrder,
    'POST /getStorageOrderShop': getStorageOrderShop,
    'POST /getStorageShopCarNum': getStorageShopCarNum,
    'POST /keepStorageInfor': keepStorageInfor,
    'GET /getStorageStaffInfo': getStorageStaffInfo,
    'POST /getSystemStorageMatchYpbh': getSystemStorageMatchYpbh
}
